/**

This file is part of MaCI/GIMnet.

MaCI/GIMnet is free software: you can redistribute it and/or modify it 
under the terms of the GNU Lesser General Public License as published 
by the Free Software Foundation, either version 3 of the License, or 
(at your option) any later version.

MaCI/GIMnet is distributed in the hope that it will be useful, but WITHOUT 
ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or 
FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public 
License for more details.

You should have received a copy of the GNU Lesser General Public 
License along with GIMnet. (See COPYING.LESSER) If not, see 
<http://www.gnu.org/licenses/>.

**/
/**
 * \file
 * \brief Handling MaCI Service information storage over MaCI::SQL interface.
 * \author Antti Maula <antti.maula@tkk.fi>
 * \author Teppo Pirttioja (DB format design)
 */
#ifndef _MACI_INTERFACE_SQL_EXTENSION_POSITIONDB_HPP_
#define _MACI_INTERFACE_SQL_EXTENSION_POSITIONDB_HPP_

#include "SQLQuery.hpp"
#include "SQLResult.hpp"
#include "SQLClient.hpp"
#include "SQLResultTypes.hpp"
#include "PositionTypes.hpp"
#include "SQLExtension.hpp"
#include <string>
#include <vector>
#include <sstream>

namespace MaCI {
  namespace SQL {
    namespace Ext {
      
      /** Single row of PositionDB type.
       */
      struct SPositionRow
      {
      public:
        /** Empty constructor.
         */
        SPositionRow(void) 
          : id(), x(), y(), a(), NULL_a(false),
            stored(), name(), sec(), us(),
            va_x(), NULL_va_x(false),
            va_y(), NULL_va_y(false),
            va_a(), NULL_va_a(false) 
        {}


        /** Constructor accepting single TSQLRow.
         */
        SPositionRow(const TSQLRow &aRow)
          : id(), x(), y(), a(), NULL_a(false),
            stored(), name(), sec(), us(), 
            va_x(), NULL_va_x(false),
            va_y(), NULL_va_y(false),
            va_a(), NULL_va_a(false) 
        {
          DecodeSQLResultRow(aRow);
        }
        
        /** Destructor.
         */
        ~SPositionRow() 
        {
        }

        
        /** This function attempts to decode a SQL Row as type
         * SPositionRow.
         *
         * This function takes a TSQLRow as parameter, and attempts to
         * decode all fields to current container. This function will
         * fail if 1) the SQLRow size doesn't match required size of
         * 11 fields.  2) if any of the integer type fields doesn't
         * correctly convert to integer.
         *
         * \note This Decoder expects to get ALL available fields. If
         * the query is made so, that result set DOESN'T contain all
         * the available fields (by selecting fields), a special
         * processing function needs to be written. (Or contents be
         * taken care of manually)
         *
         * @param[in] aRow      TSQLRow container to decode as 
         *                      SPositionRow.
         * @return              'true' if all conditions mentioned 
         *                      above are met, otherwise 'false'.
         */
        bool DecodeSQLResultRow(const TSQLRow &aRow) {
          bool result = false;

          // This is not a magic value, this is the number of fields
          // in row. Very static. Rows without 'result' check are fields
          // that can be NULL. (Not very good handling though)
          if (aRow.size() == 11) {
            result = aRow.at(0).ConvertTo(id);
            result &= aRow.at(1).ConvertTo(x);
            result &= aRow.at(2).ConvertTo(y);
            if (!aRow.at(3).ConvertTo(a)) NULL_a = true;
            result &= aRow.at(4).ConvertTo(stored);
            result &= aRow.at(5).ConvertTo(name);
            result &= aRow.at(6).ConvertTo(sec);
            result &= aRow.at(7).ConvertTo(us);
            if (!aRow.at(8).ConvertTo(va_x)) NULL_va_x = true;
            if (!aRow.at(9).ConvertTo(va_y)) NULL_va_y = true;
            if (!aRow.at(10).ConvertTo(va_a)) NULL_va_a = true;
          }
          return result;
        }


        /** Return decoded position as MaCI::TPose2D element.
         *
         * @param[out] aPose    Store pose information here.
         * @return              'true' if all required parameters exist,
         *                      'false' if not (a field was NULL)
         */
        bool GetPose2D(MaCI::Position::TPose2D &aPose) {
          aPose = MaCI::Position::TPose2D(x, y, a);
          return !NULL_a;
        }


        /** Return Timestamp as MaCI::Common:TTimestamp.
         *
         * @param[out] aStamp   Store timestamp here. 
         * @return              'true' always.
         */
        bool GetTimestamp(MaCI::Common::TTimestamp &aStamp) {
          aStamp = MaCI::Common::TTimestamp(sec, us);
          return true;
        }

      public:
        unsigned int id;
        float x;
        float y;
        float a;
        bool NULL_a;
        std::string stored;
        std::string name;
        unsigned int sec;
        unsigned int us;
        float va_x;
        bool NULL_va_x;
        float va_y;
        bool NULL_va_y;
        float va_a;
        bool NULL_va_a;
      };


      /** Array of SPositionRow elements.
       */
      typedef std::vector<SPositionRow> TPositionResultTable;


      /** Helper class for handling PositionDB storage format.
       * 
       * This is a helper class for handling 'MaCI Service' storage
       * format over the SQL interface. This storage format is not
       * directly bound to any existing interface, and hence is a good
       * example/starting point for a 'SQL Extension module'
       *
       * \note This helper class is 'Header Only' implementation.
       *
       * \see MaCI::SQL::Ext namespace for more details on helper
       * classes.
       */
      class CPositionDB : public CSQLExtension
      {
      public:
        /** Constructor.
         *
         * @param[in] aSQLClient Pointer to initialized and connect
         * SQLClient instance. This instance is used for communicating
         * with the SQL interface.
         * @param[in] aTableName Table name to query for.
         */
        CPositionDB(CSQLClient *aSQLClient, const std::string &aTargetTable)
          : CSQLExtension(aSQLClient, aTargetTable) { 
        }

        /** Destructor.
         */
        ~CPositionDB(void) {
        }
       

        /** Return array of distinct unit names, possibly selected by
         * a RegExp.
         *
         * Frontend function for the SQLClient - fetching PositionDB
         * specific data from the default table. This function
         * executes a query which lists all distinct names in the
         * Position Database. Names containing wildcards are filtered
         * out from result set. Function also allows using a Regular
         * Expression to control selection of names to list (so user
         * doesn't have to do selection on the client side)
         *
         * @param[out] aUnitsList Array of strings containing all distinct unit
         *                      names from the PositionDB interface.
         * @param[in] aRegExp   Regular expression to use for selection. Default
         *                      parameter selects all.
         * @param[in] aOffset   Offset parameter for query result set. Specifying a
         *                      value > 0 starts the result set from n:th unit 
         *                      (where n == aOffset) matching the query. Default is
         *                      zero = first result available.
         * @param[in] aRowCount Maximum number of rows to return. This limits
         *                      the number of results returned by the function.
         *                      Default value is 1000, which in this case should
         *                      always be enough.
         * @param[in] aTimeout_ms Timeout value in milliseconds. This should be
         *                      reasonably long, as the query is executed synchronously
         *                      hence the function call returns when the query 
         *                      is processed and replied.
         * @return              'true' if query was OK, and a result set was stored.
         *                      query can be OK even if result set is empty, this 
         *                      only means that no data is available with given
         *                      where clause.
         */
        bool GetUnitNameList(std::vector<std::string> &aUnitsList, 
                             const std::string &aRegExp = ".*", 
                             const unsigned int aOffset = 0,
                             const unsigned int aRowCount = 1000,
                             const unsigned int aTimeout_ms = 20000);
        

        /** Insert new Position Row.
         *
         * This functions inserts a new entry in the database. 
         *
         * 
         *
         */
        bool InsertPositionDBRow(const SPositionRow &aNewRow, 
                                 const unsigned int aTimeout_ms = 20000);

        
        // FIXME: Add here anything nifty Position specific Queries
        // you think FIXME: might be useful :) For example; utils for
        // updating existing position entry etc, whatever you like!


      private:
        CPositionDB(const CPositionDB &) 
          : CSQLExtension() {}
        CPositionDB &operator=(const CPositionDB &) { return *this; }
      };
      
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////

      bool CPositionDB::GetUnitNameList(std::vector<std::string> &aUnitsList, 
                                        const std::string &aRegExp, 
                                        const unsigned int aOffset,
                                        const unsigned int aRowCount,
                                        const unsigned int aTimeout_ms) {
        // Create and construct stream objects (even though a lot
        // slower than sprintf conversion, use this)
        std::stringstream offsetStr; offsetStr << aOffset;
        std::stringstream rowcountStr; rowcountStr << aRowCount;
        
        // Construct Query
        const std::string query("SELECT `name` from " + 
                                iTargetTable + 
                                " WHERE name NOT LIKE '%*%' "
                                "AND name REGEXP '" + aRegExp + "' "
                                "GROUP BY `name` "
                                "ORDER BY `name` "
                                "LIMIT " + 
                                offsetStr.str() + 
                                " , " + 
                                rowcountStr.str()); 
        
        // Execute Query.
        CSQLResult sqlresult;
        CSQLQuery sqlquery(query);
        bool result = iSQLClient->ExecuteQuery(sqlresult, sqlquery, aTimeout_ms);
        if (result) {
          aUnitsList.clear();
          
          // Check that result contains a table.
          if (sqlresult.GotTable()) {
            
            // Get table reference for easier (faster) access.
            const CSQLTable &t = sqlresult.GetTable();
            
            // Get rowcount and iterate rows.
            const unsigned int rowcount = t.GetRowCount();
            
            // Loop through all rows, watch those results!
            for (unsigned int i = 0; 
                 i < rowcount && result; 
                 ++i) {
              const CSQLField &f = t.GetField(i, 0);
              aUnitsList.push_back(f.GetValueString());
            }
          } else if (sqlresult.GotError()) { 
            // Query Failed! :(
            iLastError = sqlresult.GetLastError();
            result = false;

          } else {
            iLastError = "Unknown error!";
            result = false;

          }

        } else {
          iLastError = "Query failed or timed out";
          dPrint(ODWARN,"Query failed or timed out :(");
            
        }
        return result;
      }
      /////////////////////////////////////////////////////////////////////////

      bool CPositionDB::InsertPositionDBRow(const SPositionRow &aNewRow, const unsigned int aTimeout_ms)
      {
        bool result = true;
        std::stringstream querys;
        querys << 
          "INSERT INTO " << 
          iTargetTable << 
          " VALUES (" << 
          "NULL" << 
          ", '" << 
          aNewRow.x << 
          "', '" << 
          aNewRow.y << 
          "', '" <<
          aNewRow.a << 
          "', CURRENT_TIMESTAMP , '" << 
          aNewRow.name << 
          "', '" << 
          aNewRow.sec << 
          "', '" << 
          aNewRow.us << 
          "', '" << 
          aNewRow.va_x << 
          "', '" << 
          aNewRow.va_y << 
          "', '" << 
          aNewRow.va_a << 
          "')";
        std::string query(querys.str());
        //        dPrint(ODTEST,"Query:\n'%s'", query.c_str());
        
        CSQLResult sqlresult;
        CSQLQuery sqlquery(query);
        result = iSQLClient->ExecuteQuery(sqlresult, sqlquery, aTimeout_ms);
        if (result) {
          // Check that result contains a table.
          if (sqlresult.GotAffectedRows()) {
            dPrint(ODTEST,"Affected rows %lld, all OK.", sqlresult.GetAffectedRows());
            result = sqlresult.GetAffectedRows() == 1;

          } else if (sqlresult.GotError()) { 
            // Query Failed! :(
            iLastError = sqlresult.GetLastError();
            result = false;

          } else {
            iLastError = "Unknown error!";
            result = false;

          }

        } else {
          iLastError = "Query failed or timed out";
          dPrint(ODWARN,"Query failed or timed out :(");
            
        }
        return result;
      }
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
    }
  }
}
#endif // _MACI_INTERFACE_SQL_EXTENSION_POSITIONDB_HPP_
